if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCertificationQuestionnaireAnswers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetCertificationQuestionnaireAnswers]
GO

/******************************************************************
* Name:			GetCertificationQuestionnaireAnswers
* Purpose:		Retrieves Training Questionnaire Answer s
*
* PARAMETERS
* Name    		Description					
* -------------  	-------------------------------------------
* @admPersonId	
* @questionnaireName
******************************************************************/

Create Procedure [dbo].[GetCertificationQuestionnaireAnswers] (    
	@admPersonId	INTEGER,  
	@questionnaireName varchar(256)
)    
AS    
BEGIN    
    
	SET NOCOUNT ON    
    
	SELECT QA.Id,  
     		QA.QuestionnaireVersionId,   
     		QA.Name,  
    		QA.Required,  
     		QA.Editable,  
     		Q.Category as 'QuestionnaireCategory',  
     		QA.LastModifiedDate,  
     		QA.ExternalId,  
     		QA.IsClone,  
     		QA.ActionTypeId,  
     		QA.Highlight,
	 	QA.LastModifiedByAdmPersonId ,
	 	ADP.LastName+', '+ ADP.FirstName  as LastModifiedBy
	FROM AdmPerson_QuestionnaireAnswer_Map map
		INNER JOIN QuestionnaireAnswer QA ON map.QuestionnaireAnswerId = QA.Id
		inner join QuestionnaireVersion QV on QV.[Id] = QA.QuestionnaireVersionId
		INNER JOIN Questionnaire Q ON Q.Id = QV.QuestionnaireId
		LEFT JOIN AdmPerson ADP
	  		on ADP.Id = QA.LastModifiedByAdmPersonId 
	WHERE QA.Name like @questionnaireName + '%'
		AND Map.AdmPersonId = @admPersonId		
      
END    

GO

